package com.egao.common.module.system.transfer;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.egao.common.core.web.JsonResult;
import com.egao.common.module.system.entity.QsSchool;
import com.egao.common.module.system.mapper.QsSchoolMapper;
import com.egao.common.module.system.service.QsSchoolService;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.FileInputStream;
import java.util.List;

/**
 * 海外学校excel数据导入
 * 至少需要需要7条州数据
 * id 1~7 为大洲 1-6 亚洲-大洋洲 7其他州
 * @author pyx
 */
@RestController
@Slf4j
public class SchoolExcleController {
    @Autowired(required = false)
    private QsSchoolMapper qsSchoolMapper;

    @ApiImplicitParams({
            @ApiImplicitParam(name = "url", value = "文件所在路径", required = true, dataType = "String", paramType = "query"),
    })
    public JsonResult sqSchool(String url) throws Exception {
        if(StrUtil.isBlank(url)){
            return JsonResult.error("没有获取到文件地址");
        }

//        1.通过流读取Excel文件
        FileInputStream inputStream = new FileInputStream(url);
//        2.通过poi解析流 HSSFWorkbook 处理流得到的对象中 就封装了Excel文件所有的数据
        Workbook workbook = WorkbookFactory.create(inputStream);
//        3.从文件中获取表对象  getSheetAt通过下标获取
        Sheet sheet = workbook.getSheetAt(0);
//        4.从表中获取到行数据  从第二行开始 到 最后一行  getLastRowNum() 获取最后一行的下标
        int lastRowNum = sheet.getLastRowNum();

//        找到所有大洲
        QueryWrapper<QsSchool> continentWrapper = new QueryWrapper<>();
        continentWrapper.in("id",1,2,3,4,5,6,7);
        List<QsSchool> continentList = qsSchoolMapper.selectList(continentWrapper);

//        找到所有国家
        QueryWrapper<QsSchool> countryWrapper = new QueryWrapper<>();
        countryWrapper.in("parent_id",1,2,3,4,5,6,7);
        List<QsSchool> countryList = qsSchoolMapper.selectList(countryWrapper);

//        找到所有学校
        List<QsSchool> schoolList = qsSchoolMapper.selectList(null);
        schoolList.removeAll(countryList);
        schoolList.removeAll(continentList);

        int number = 0;

        for (int i = 1; i <= lastRowNum; i++) {

            QsSchool qsCountry = new QsSchool();
            QsSchool qsSchool = new QsSchool();

            boolean bec = true;
            boolean bes = true;

//            通过下标获取行
            Row row = sheet.getRow(i);
//            从行中获取数据
            String country = row.getCell(3).getStringCellValue();
            if("香港".equals(country)){
                country = "中国香港";
            }
            if("台湾".equals(country)){
                country = "中国台湾";
            }
            String parentName = row.getCell(4).getStringCellValue();
            String school= row.getCell(7).getStringCellValue();

//            如果国家集合没有，则添加国家
            for (QsSchool ctr:countryList) {
                if(country.equals(ctr.getName())){
                    bec = false;
                }
            }
            if(bec){
                if(!"中国".equals(country)){
                    qsCountry.setName(country);
                    Integer parentId = parentId(parentName);
                    qsCountry.setParentId(parentId);
                    qsSchoolMapper.insert(qsCountry);
                    countryList.add(qsCountry);
                    number++;
                }
            }

//            查重添加学校
            for (QsSchool sch:schoolList) {
                if(school.equals(sch.getName())){
                    bes = false;
                }
            }
            if(bes){
                if(!"中国".equals(country)){
                    qsSchool.setName(school);
                    Integer parentId = parentId(country);
                    qsSchool.setParentId(parentId);
                    qsSchoolMapper.insert(qsSchool);
                    schoolList.add(qsSchool);
                    number++;
                }
            }

        }
        return JsonResult.ok("新增：" + number + "条数据");
    }

//    根据父类名称查询父类id
    private Integer parentId(String parentName){
        QueryWrapper<QsSchool> wrapper = new QueryWrapper<>();
        wrapper.eq("name",parentName);
        QsSchool qsSchool = qsSchoolMapper.selectOne(wrapper);
        return qsSchool.getId();
    }

}
